package cn.edu.lingnan.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import cn.edu.lingnan.model.PageBean;
import cn.edu.lingnan.model.Taxicar;
import cn.edu.lingnan.util.DateUtil;
import cn.edu.lingnan.util.StringUtil;

public class TaxicarDao {

	public ResultSet taxicarList(Connection con,PageBean pageBean,Taxicar taxicar,String bbirthday,String ebirthday)throws Exception{
		StringBuffer sb=new StringBuffer("select * from t_taxicar s,t_consumer g where s.consumerId=g.id");
		if(StringUtil.isNotEmpty(taxicar.getStuNo())){
			sb.append(" and s.stuNo like '%"+taxicar.getStuNo()+"%'");
		}
		if(StringUtil.isNotEmpty(taxicar.getStuName())){
			sb.append(" and s.stuName like '%"+taxicar.getStuName()+"%'");
		}
		if(StringUtil.isNotEmpty(taxicar.getSex())){
			sb.append(" and s.sex ='"+taxicar.getSex()+"'");
		}
		if(taxicar.getConsumerId()!=-1){
			sb.append(" and s.consumerId ='"+taxicar.getConsumerId()+"'");
		}
		if(StringUtil.isNotEmpty(bbirthday)){
			sb.append(" and TO_DAYS(s.birthday)>=TO_DAYS('"+bbirthday+"')");
		}
		if(StringUtil.isNotEmpty(ebirthday)){
			sb.append(" and TO_DAYS(s.birthday)<=TO_DAYS('"+ebirthday+"')");
		}
		if(pageBean!=null){
			sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows());
		}
		PreparedStatement pstmt=con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}
	
	public int taxicarCount(Connection con,Taxicar taxicar,String bbirthday,String ebirthday)throws Exception{
		StringBuffer sb=new StringBuffer("select count(*) as total from t_taxicar s,t_consumer g where s.consumerId=g.id");
		if(StringUtil.isNotEmpty(taxicar.getStuNo())){
			sb.append(" and s.stuNo like '%"+taxicar.getStuNo()+"%'");
		}
		if(StringUtil.isNotEmpty(taxicar.getStuName())){
			sb.append(" and s.stuName like '%"+taxicar.getStuName()+"%'");
		}
		if(StringUtil.isNotEmpty(taxicar.getSex())){
			sb.append(" and s.sex ='"+taxicar.getSex()+"'");
		}
		if(taxicar.getConsumerId()!=-1){
			sb.append(" and s.consumerId ='"+taxicar.getConsumerId()+"'");
		}
		if(StringUtil.isNotEmpty(bbirthday)){
			sb.append(" and TO_DAYS(s.birthday)>=TO_DAYS('"+bbirthday+"')");
		}
		if(StringUtil.isNotEmpty(ebirthday)){
			sb.append(" and TO_DAYS(s.birthday)<=TO_DAYS('"+ebirthday+"')");
		}
		PreparedStatement pstmt=con.prepareStatement(sb.toString());
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			return rs.getInt("total");
		}else{
			return 0;
		}
	}
	
	public int taxicarDelete(Connection con,String delIds)throws Exception{
		String sql="delete from t_taxicar where stuId in("+delIds+")";
		PreparedStatement pstmt=con.prepareStatement(sql);
		return pstmt.executeUpdate();
	}
	
	public int taxicarAdd(Connection con,Taxicar taxicar)throws Exception{
		String sql="insert into t_taxicar values(null,?,?,?,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, taxicar.getStuNo());
		pstmt.setString(2, taxicar.getStuName());
		pstmt.setString(3, taxicar.getSex());
		pstmt.setString(4, DateUtil.formatDate(taxicar.getBirthday(), "yyyy-MM-dd"));
		pstmt.setInt(5, taxicar.getConsumerId());
		pstmt.setString(6, taxicar.getEmail());
		pstmt.setString(7, taxicar.getStuDesc());
		return pstmt.executeUpdate();
	}
	
	public int taxicarModify(Connection con,Taxicar taxicar)throws Exception{
		String sql="update t_taxicar set stuNo=?,stuName=?,sex=?,birthday=?,consumerId=?,email=?,stuDesc=? where stuId=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, taxicar.getStuNo());
		pstmt.setString(2, taxicar.getStuName());
		pstmt.setString(3, taxicar.getSex());
		pstmt.setString(4, DateUtil.formatDate(taxicar.getBirthday(), "yyyy-MM-dd"));
		pstmt.setInt(5, taxicar.getConsumerId());
		pstmt.setString(6, taxicar.getEmail());
		pstmt.setString(7, taxicar.getStuDesc());
		pstmt.setInt(8, taxicar.getStuId());
		return pstmt.executeUpdate();
	}
	
	public boolean getTaxicarByConsumerId(Connection con,String consumerId)throws Exception{
		String sql="select * from t_taxicar where consumerId=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, consumerId);
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			return true;
		}else{
			return false;
		}
	}
}
